package com.zhht.checkcop.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Formatter;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

public class MysqlDb {
	private static Connection conn = null;
	private static Statement stmt = null;
	private static PreparedStatement prst = null;
	private static ResultSet rst = null;
	private static ResultSetMetaData rsmd = null;
	private static PooledDBA connPool = null;
	
	public static Formatter fmt = new Formatter();
	private final static Logger log= Logger.getLogger(MysqlDb.class);
	
	static {
		connPool = new PooledDBA();
	}
	
 
    public static void main(String[] args) throws Exception {
    	MysqlDb mysqldb = new MysqlDb();
    	mysqldb.connect();
//    	List<Map<String, Object>> list = mysqldb.query("select id,name,author from t_gf_book where kindsId in (5,6) and IFNULL(author,'') != ''  AND sync = 0");
    	List<Map<String, Object>> list = mysqldb.query("select tgb.id,tgb.name,tgb.author from t_gf_book tgb where tgb.kindsId in (5,6) and IFNULL(tgb.author,'') = '' AND tgb.sync = 0 AND EXISTS ( SELECT 'x' FROM chapter c WHERE c.bookname = tgb. NAME and IFNULL(c.content, '') != '')  LIMIT 650,650");
    	for(Map<String,Object> mm:list){
    		System.out.println("更新："+mm.get("name")+"   bookId:"+mm.get("id")+"   author:"+mm.get("author"));
    		try{
//    		List<Map<String, Object>> chapterlist = mysqldb.query("select name,annotation,content,comment,source_url,sort from `chapter` where bookname = '"+mm.get("name")+"'  AND author = '崔鸿' AND author = '崔鸿' AND author = '"+mm.get("author")+"' ORDER BY sort");
    		List<Map<String, Object>> chapterlist = mysqldb.query("select name,annotation,content,comment,source_url,sort from `chapter` where bookname = '"+mm.get("name")+"'  ORDER BY sort");
    		int insert=0;
    		int contents=0;
    		for(Map<String,Object> chapter:chapterlist){
    			Map<String,Object> gfChapter=new HashMap<String,Object>();
    			gfChapter.put("name",chapter.get("name"));
    			gfChapter.put("bookid",mm.get("id"));
    			gfChapter.put("author",mm.get("author"));
    			gfChapter.put("annotation",chapter.get("annotation"));
    			String content = chapter.get("content")+"";
    			gfChapter.put("content",content);
    			contents +=content.length()>0?content.trim().length():0;
    			gfChapter.put("words_num",content.length()>0?content.trim().length():0);
    			gfChapter.put("comment",chapter.get("comment"));
    			gfChapter.put("source_url",chapter.get("source_url"));
    			gfChapter.put("bookname",mm.get("name"));
    			gfChapter.put("sort",chapter.get("sort"));
    			insert += mysqldb.insert("t_gf_chapter",gfChapter);
    		}
    		if(insert>0){
    			mysqldb.execute("update t_gf_book set sync = 1,words_num = "+contents+" where id = "+mm.get("id"));
    		}
    		}catch (Exception e) {
    			System.out.println(e.toString());
			}
    	}
    }

    //构造函数
    public MysqlDb() throws Exception {}
    
    //获取连接
    public void connect() throws Exception {
    	conn = connPool.getConnection();
    	if(conn == null) 
    		throw new Exception("连接不存在");
    	return ;
    }
    
    //回收连接
    public void recycleConnect() throws Exception {
    	connPool.close(conn);
    }
    
    //当参数不都是字符串时可以通过此方法查询
    public List<Map<String,Object>> query(String sql, List<Object> params) throws Exception {
    	log.info(sql+"\n"+params);
        List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();

        try{
            prst = conn.prepareStatement(sql);
            int i = 1;
            if(params.size() != 0) {
                for(Object param : params) {
                    prst.setObject(i++, param);
                }
            }
            rst = prst.executeQuery();
            rsmd = rst.getMetaData();          

            //共几列
            int columnCount = rsmd.getColumnCount();
            while(rst.next()) {
                Map<String,Object> map = new HashMap<String,Object>();
                for(i = 0;i < columnCount;i++) {
                    map.put(rsmd.getColumnLabel(i+1).toString(),getValueByFieldClass(rst ,rsmd ,i+1));
                }
                dataList.add(map);
            }
             
        } catch (SQLException e) {
            throw e;
        }
        return dataList;
    }
    
    //当参数不都是字符串时可以通过此方法查询
    public List<String> queryListStr(String sql) throws Exception {
    	log.info(sql);
    	List<String> dataList = new ArrayList<String>();
    	
    	try{
    		prst = conn.prepareStatement(sql);
    		rst = prst.executeQuery();
    		rsmd = rst.getMetaData();          
    		
    		while(rst.next()) {
    			dataList.add(getValueByFieldClass(rst ,rsmd ,1)+"");
    		}
    		
    	} catch (SQLException e) {
    		throw e;
    	}
    	return dataList;
    }
    
    //查询一条数据
    public Map<String,Object> first(String sql, List<Object> param) throws Exception {
    	List<Map<String,Object>> data = query(sql,param);
    	
    	if(data ==null || data.isEmpty()) {
    		log.info("查询sql为：" + sql + ",参数为：" + param.toString() + "的查询条件的数据集为空");
    		return null;
    	}
    	
    	if(data.size() > 1) {
    		log.info("查询sql为：" + sql + ",参数为：" + param.toString() + "的查询条件的数据集大于一条");
    		throw new Exception("数据集大于一条");
    	}
    		
    	//返回结果
    	return data.get(0);
    }
    
    
    //当参数不都是字符串时可以通过此方法查询
    public ResultSet queryToResultSet(String sql) throws Exception {
        try{
            prst = conn.prepareStatement(sql);
            rst = prst.executeQuery();
        }
        catch (Exception e) {
        	e.printStackTrace();
		}
        return rst;
    }
    
    //执行原生sql查询返回list
    public List<Map<String,Object>> query(String sql) throws Exception {
    	log.info(sql);
        List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();

        try{
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sql);
            rsmd = rst.getMetaData();          

            //共几列
            int columnCount = rsmd.getColumnCount();
            while(rst.next()) {
                Map<String,Object> map = new HashMap<String,Object>();
                for(int i = 0;i < columnCount;i++) {
                    map.put(rsmd.getColumnLabel(i+1).toString(),getValueByFieldClass(rst ,rsmd ,i+1));
                }
                dataList.add(map);
            }
             
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception("SQLException: " + e.getMessage());
        }

        return dataList;
    }
    
    /**
     * 专门为对账文件查询数据库实现的一个方法。因为用上面的query方法会在getValueByFieldClass方法中报空指针的错
     * @param sql
     * @return
     * @throws Exception
     */
    public List<Map<String,Object>> query4DealWithFile(String sql) throws Exception {
    	log.info(sql);

        List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();

        try{
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sql);
            rsmd = rst.getMetaData();          

            //共几列
            int columnCount = rsmd.getColumnCount();
            
            while(rst.next()) {
                Map<String,Object> map = new HashMap<String,Object>();
                for(int i = 0;i < columnCount;i++) {
                    map.put(rsmd.getColumnLabel(i+1).toString(),rst.getObject(i+1));
                }
                dataList.add(map);
            }
             
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception("SQLException: " + e.getMessage());
        }

        return dataList;
    }
    
    
    //查询结果以map方式存储---当参数类型不清楚时,清楚时也可以用
    public Map<String ,Map<String,Object>> query(String sql, List<Object> params, String key) throws Exception {
    	log.info(sql + "\n参数为" + params);

        Map<String, Map<String,Object>> dataList = new HashMap<String, Map<String,Object>>();
        
        prst = conn.prepareStatement(sql);
        int i = 1;
        if(params!=null&&params.size() != 0) {
        	for(Object param : params) {
        		prst.setObject(i++,param);
        	}
        }
        rst = prst.executeQuery();
        rsmd = rst.getMetaData();          
        
        //共几列
        int columnCount = rsmd.getColumnCount();
        while(rst.next()) {
        	Map<String,Object> map = new HashMap<String,Object>();
        	String field_value = "";
        	for(i = 0;i < columnCount;i++) {
        		String field = rsmd.getColumnLabel(i+1).toString();
        		map.put(field,getValueByFieldClass(rst ,rsmd ,i+1));
        		if(!key.equals(field)) continue;
        		Object value = getValueByFieldClass(rst, rsmd, i+1);
        		if(value != null) field_value = value.toString();
        	}
        	//map的key
        	if("".equals(field_value)) {
        		throw new Exception("该字段[" + key + "]无法作为key");
        	}
        	dataList.put(field_value,map);
        }

        return dataList;
    }
    
    //查询结果以map方式存储---当参数类型不清楚时,清楚时也可以用
    public Map<String ,Map<String,Object>> query(String sql, List<Object> params, String key1, String key2) throws Exception {
    	log.info(sql + "\n参数为" + params);
    	
    	Map<String, Map<String,Object>> dataList = new HashMap<String, Map<String,Object>>();
    	
    	prst = conn.prepareStatement(sql);
    	int i = 1;
    	if(params!=null&&params.size() != 0) {
    		for(Object param : params) {
    			prst.setObject(i++,param);
    		}
    	}
    	rst = prst.executeQuery();
    	rsmd = rst.getMetaData();          
    	
    	//共几列
    	int columnCount = rsmd.getColumnCount();
    	while(rst.next()) {
    		Map<String,Object> map = new HashMap<String,Object>();
    		String field_value = "";
    		for(i = 0;i < columnCount;i++) {
    			String field = rsmd.getColumnLabel(i+1).toString();
    			map.put(field,getValueByFieldClass(rst ,rsmd ,i+1));
    			if(key1.equals(field)||key2.equals(field)){
	    			Object value = getValueByFieldClass(rst, rsmd, i+1);
	    			if(value != null) field_value = field_value + value.toString()+",";
    			}
    		}
    		//map的key
    		if("".equals(field_value)) {
    			throw new Exception("该字段[" + key1 + "]无法作为key");
    		}
    		dataList.put(field_value,map);
    	}
    	
    	return dataList;
    }
    
    //执行原生sql以map方式存储
    public Map<Object ,Map<String,Object>> query(String sql, String key) throws Exception {
    	log.info(sql);
        Map<Object, Map<String,Object>> dataList = new HashMap<Object, Map<String,Object>>();

        stmt = conn.createStatement();
        rst = stmt.executeQuery(sql);
        rsmd = rst.getMetaData();          
        
        //共几列
        int columnCount = rsmd.getColumnCount();
        while(rst.next()) {
        	Map<String,Object> map = new HashMap<String,Object>();
        	Object field_value = null;
        	for(int i = 0;i < columnCount;i++) {
        		String field = rsmd.getColumnLabel(i+1).toString();
        		map.put(field,getValueByFieldClass(rst ,rsmd ,i+1));
        		if(!key.equals(field)) continue;
        		field_value = getValueByFieldClass(rst, rsmd, i+1);
        	}
        	//map的key
        	if(null == field_value) {
        		throw new Exception("该字段[" + key + "]无法作为key");
        	}
        	dataList.put(field_value,map);
        }

        return dataList;
    }
    
    public Map<String ,Map<String,Object>> squery(String sql, String key) throws Exception {
    	log.info(sql);
        Map<String, Map<String,Object>> dataList = new HashMap<String, Map<String,Object>>();

        stmt = conn.createStatement();
        rst = stmt.executeQuery(sql);
        rsmd = rst.getMetaData();          
        
        //共几列
        int columnCount = rsmd.getColumnCount();
        while(rst.next()) {
        	Map<String,Object> map = new HashMap<String,Object>();
        	String field_value = "";
        	for(int i = 0;i < columnCount;i++) {
        		String field = rsmd.getColumnLabel(i+1).toString();
        		map.put(field,getValueByFieldClass(rst ,rsmd ,i+1));
        		if(!key.equals(field)) continue;
        		field_value = getValueByFieldClass(rst, rsmd, i+1).toString();
        	}
        	//map的key
        	if(null == field_value) {
        		throw new Exception("该字段[" + key + "]无法作为key");
        	}
        	dataList.put(field_value,map);
        }

        return dataList;
    }

    //根据字段类型调用不同的方法去字段值
    private static Object getValueByFieldClass(ResultSet resultSet,ResultSetMetaData rsMetaData,int i) throws Exception {
        String class_name = null; 
        Object ret_obj = null;
    	class_name = rsMetaData.getColumnClassName(i).toString();
        if(class_name.equals("java.lang.Integer") || class_name.equals("java.lang.Boolean") 
        		|| class_name.equals("java.math.BigInteger")) {
            ret_obj = resultSet.getInt(rsMetaData.getColumnLabel(i)); 
        } else if(class_name.equals("java.lang.String")) {
            ret_obj = resultSet.getString(rsMetaData.getColumnLabel(i)); 
        } else if(class_name.equals("java.math.BigDecimal")) {
            ret_obj = resultSet.getBigDecimal(rsMetaData.getColumnLabel(i)); 
            if(ret_obj!=null&&ret_obj.toString().equals("0E-8"))
            	return "0.00000000";
        } else if(class_name.equals("java.lang.Long")) {
        	ret_obj = resultSet.getLong((rsMetaData.getColumnLabel(i))); 
        }else if(class_name.equals("java.sql.Timestamp")) {
        	java.util.Date date= resultSet.getTimestamp(rsMetaData.getColumnLabel(i)); 
        	ret_obj =date;
        }
        return ret_obj;
    }

    //执行插入和更新
    public int execute(String sql, List<String> params) throws Exception {
    	log.info(sql + "\n参数为" + params);
        int effect_num = 0;
        prst = conn.prepareStatement(sql);
        int i = 1;
        if(params!=null&&params.size() != 0) {
            for(String param : params) {
                prst.setString(i++,param);
            }
        }
        effect_num = prst.executeUpdate();
        return effect_num;
    }
    
    //执行插入和更新，不清楚字段类型时
    public int execute(String sql, List<Object> params,Boolean fuzzy) throws Exception {
    	log.info(sql + "\n参数为" + params);
        int effect_num = 0;
        prst = conn.prepareStatement(sql);
        int i = 1;
        if(params != null && params.size() != 0) {
            for(Object param : params) {
                prst.setObject(i++, param);
            }
        }
        
        effect_num = prst.executeUpdate();
        return effect_num;
    }
    
    //执行插入
    public int insert(String table, Map<String,Object> fieldsValue) throws Exception {
    	List<Object> params = new ArrayList<Object>();
		List<String> tables_headers = prepareInsertSql(fieldsValue,params);
		
		//组装sql
		String insert_sql = "INSERT INTO " + table + " (" + tables_headers.get(0) + ") VALUES(" + tables_headers.get(1) + ") ;";
		
		//执行结果
		int effect_num = 0;
		effect_num = execute(insert_sql, params,true);
        return effect_num;
    }
    
    //处理数据
  	protected static List<String> prepareInsertSql(Map<String,Object> fields,List<Object> params) throws Exception {
  		List<String> table_fields = new ArrayList<String>();
  		String values = "";
  		
  		for(String field : fields.keySet()) {
  			table_fields.add("`" + field + "`");
  			params.add(fields.get(field));
  			values += ",?";
  		}
  		
  		String table_headers = table_fields.toString();
  		table_headers = table_headers.substring(1, table_headers.length() - 1);
  		
  		//组合结果返回
  		List<String> p_insert = new ArrayList<String>();
  		p_insert.add(table_headers);
  		p_insert.add(values.substring(1));
  		return p_insert;
  	}
    
  	/**
     * 可以执行添加操作 返回id
     * @param tableName 
     * @param Map<String,String>
     * @return
     */
    public String executeInsertById(String tableName, Map<String,Object> params) throws Exception {
        return execDB(assemblyInsertSql(tableName,params));
    }
    
    /**
     * 可以执行添加操作 返回id
     * @param tableName 
     * @param Map<String,String>
     * @return
     */
    public String executeInsertByIds(String tableName, Map<String,Object> params) throws Exception {
        return execDB(assemblyInsertSql(tableName,params));
    }
    
    public String assemblyInsertSql(String tableName, Object obj)
    {
    	Map params=(Map) obj;
    	String sql="insert into "+tableName;
        String paramHead="(";
        String paramEnd=" value(";
    	Set<String> key = params.keySet();
        for (Iterator it = key.iterator(); it.hasNext();) {
            String s = (String) it.next();
            if(s!=null&&!s.equals("")){
            	Object field_value = params.get(s);
            	if(field_value == null) continue;
            	paramHead+=s+",";
            	paramEnd+="'" + field_value + "',";
            }
        }
        paramHead=paramHead.substring(0,paramHead.length()-1);
        paramEnd=paramEnd.substring(0,paramEnd.length()-1);
        sql+=paramHead+")"+paramEnd+")";
        
    	return sql;
    }
    
    public String[] assemblyInsertSqls(String tableName, Object obj)
    {
    	String[] strSql=new String[3];
    	Map params=(Map) obj;
    	String sql="insert into "+tableName;
        String paramHead="(";
        String paramEnd="(";
    	Set<String> key = params.keySet();
        for (Iterator it = key.iterator(); it.hasNext();) {
            String s = (String) it.next();
            if(s!=null&&!s.equals("")){
            	String str=params.get(s)+"";
            	paramHead+=s+",";
            	paramEnd+="'"+str+"',";
            }
        }
        paramHead=paramHead.substring(0,paramHead.length()-1);
        paramEnd=paramEnd.substring(0,paramEnd.length()-1);
        sql+=paramHead+") value ";
        strSql[0]=sql;
        strSql[1]=paramEnd+")";
        strSql[2]=sql+paramEnd+")"; 
    	return strSql;
    }
    
    /**
     * 判断字符串中是否包含汉字
     * @param str
     * @return
     */
    public static boolean isChinese(String str) {
		Pattern pattern =Pattern.compile("[\u4E00-\u9FA5]");
	    Matcher matcher = pattern.matcher(str);
		while (matcher.find()) {
		  	return true;
		}
		return false;
	}
    
    public String assemblyUpdateSql(String tableName, Map<String,Object> params, Map<String,String[]> conditionMap,String sqlAdditional,Boolean bool)
    {
    	String sql="update "+tableName;
        String paramHead=" set ";
        String paramEnd="";
    	if(params!=null&&params.size()<=0)return "";
    	
    	Set<String> key = params.keySet();
        for (Iterator it = key.iterator(); it.hasNext();) {
            String s = (String) it.next();
            if(s!=null&&!s.equals("")){
            	String str=params.get(s)+"";
            	//没有加减算法就
            	if((str.indexOf("+")>0||str.indexOf("-")>0)&&bool){
            		if(isChinese(str))
            			paramHead+=s+"='"+str+"',";
            		else
            			paramHead+=s+"="+str+",";
            	}else
            		paramHead+=s+"='"+str+"',";
            }
        }
        paramHead=paramHead.substring(0,paramHead.length()-1);

        if(conditionMap!=null&&conditionMap.size()>0)
    	{
        	paramEnd+=" where";
    		Set<String> keys = conditionMap.keySet();
	        for (Iterator it = keys.iterator(); it.hasNext();) {
	            String s = (String) it.next();
	            if(s!=null&&!s.equals("")){
	            	String[] str=conditionMap.get(s);
	            	if(str.length==2)
	            	{
	            		paramEnd+=" "+s+" "+str[0]+" "+str[1] +" and";
	            	}
	            }
	        }
	        paramEnd=paramEnd.substring(0,paramEnd.length()-4);
    	}

    	if(sqlAdditional!=null&&!sqlAdditional.equals(""))
    	{
    		if(conditionMap.size()>0)
    			paramEnd+=sqlAdditional;
    		else
    			paramEnd+=" where "+sqlAdditional;
    	}
    	sql+=paramHead+paramEnd;
    	System.out.println(sql);
    	return sql;
    }
    
    //更新数据
    public int update(String tablename, Map<String,Object> replace_value,Map<String,Object> add_value, Map<String,Object> condition) throws Exception {
    	//组合sql
    	String update_sql = "";
    	List<Object> field_value_param = new ArrayList<Object>();
		update_sql = buildUpdateSql(tablename, replace_value, add_value, condition, field_value_param);
    	
    	//声明更新条数
    	int effect_num = 0;
    	effect_num = execute(update_sql,field_value_param,true);
    	
    	return effect_num;
    }
    
    //构造更新sql，累加替换和更新替换
    public static String buildUpdateSql(String tablename, Map<String,Object> replace_value, Map<String,Object> add_value,Map<String,Object> condition,List<Object> all_field_value) throws Exception {
    	//声明sql
    	String update_sql = "Update " + tablename + " SET ";
    	
    	//字段值的更新替换设置
    	if(replace_value != null && !replace_value.isEmpty())
	    	for(String field : replace_value.keySet()) {
	    		update_sql += "`" + field + "` = ? ,"; 
	    		all_field_value.add(replace_value.get(field));
	    	}
    	
    	//累加字段的值
    	if(add_value != null && !add_value.isEmpty())
			for(String field : add_value.keySet()) {
	    		update_sql += "`" + field + "` = `" + field + "` + ? ,"; 
	    		all_field_value.add(add_value.get(field));
	    	}
    	update_sql = update_sql.substring(0, update_sql.length() - 1);
    	update_sql += " WHERE ";
    	
    	//更新条件
    	for(String field : condition.keySet()) {
    		update_sql += " `" + field + "` = ? AND";
    		all_field_value.add(condition.get(field));
    	}
    	update_sql = update_sql.substring(0, update_sql.length() - 3);
    	return update_sql;
    }
    
    /**
     * 可以执行修改操作
     * @param tableName
     * @param head params
     * @param 条件 conditionMap
     * @param 追加条件 sqlAdditional
     * @return
     */
    public int executeUpdate(String tableName, Map<String,Object> params, Map<String,String[]> conditionMap,String sqlAdditional) throws Exception {
    	return execute(assemblyUpdateSql(tableName, params, conditionMap, sqlAdditional,true));
    }
    
    //执行原生sql
    public int execute(String sql) throws Exception {
    	log.info(sql);
        int effect_num = 0;
        
        stmt = conn.createStatement();
        effect_num = stmt.executeUpdate(sql);
        
        return effect_num;
    }
    
    public String execDB(String sql) throws Exception
    {	
    	log.info(sql);
    	
    	String effect_num = "";
    	ResultSet rs = null;
        prst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        effect_num = prst.executeUpdate()+"";
        rs=prst.getGeneratedKeys();
        if(rs.next())
        {
        	effect_num = rs.getString(1);
        }
        return effect_num;
    }
    
    /**
     * @param sql
     * @param Map中是String[]  key 是 列名 string[0] 是比较操作符 如： like
     * 									string[1] 是列 对应的值 
     * @param sql补充参数 添加在sql最外层 
     * @return List<Map<String,Object>>
     * @throws Exception 
     */
    public List<Map<String,Object>> query(String sql, Map<String,String[]> params,String sqlAdditional) throws Exception {

        List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();

        try{
        	if(params!=null&&params.size()>0)
        	{
        		sql+=" where";
        		Set<String> key = params.keySet();
    	        for (Iterator it = key.iterator(); it.hasNext();) {
    	            String s = (String) it.next();
    	            if(s!=null&&!s.equals("")){
    	            	String[] str=params.get(s);
    	            	if(str.length==2)
    	            	{
    	            		sql+=" "+s+" "+str[0]+" "+str[1];
    	            	}
    	            }
    	        }
        	}

        	if(sqlAdditional!=null&&!sqlAdditional.equals(""))
        	{
        		if(params.size()>0)
        			sql+=sqlAdditional;
        		else
        			sql+=" where "+sqlAdditional;
        	}
        	log.info(sql);
        	
            prst = conn.prepareStatement(sql);
            rst = prst.executeQuery();
            rsmd = rst.getMetaData();          
            //共几列
            int columnCount = rsmd.getColumnCount();
            while(rst.next()) {
                Map<String,Object> map = new HashMap<String,Object>();
                for(int i = 0;i < columnCount;i++) {
                    map.put(rsmd.getColumnLabel(i+1).toString(),getValueByFieldClass(rst ,rsmd ,i+1));
                }
                dataList.add(map);
            }
             
        } catch (SQLException e) {
            throw e;
        }
        return dataList;
    }
    
    public List<Map<String,Object>> getQueryTable(String paramString,Map map) throws Exception
	{
		if(map!=null&&map.size()>0)
		{
			paramString+=" where";
			Set<String> keys = map.keySet();
	        for (Iterator it = keys.iterator(); it.hasNext();) {
	            String s = (String) it.next();
	            if(s!=null&&!s.equals("")){
	            	String str=map.get(s).toString();
	            	if(!s.equals("trans_passwd"))
	            		paramString+=" "+s+" = '"+str +"' and";
	            }
	        }
	        paramString=paramString.substring(0,paramString.length()-4);
		}
		return query(paramString);
	}
    
    public static int[] executeBatch(List<String> sqls) throws Exception
    {
	     if(sqls==null)
	    	 return null;
	     stmt=conn.createStatement();
	     for(String sql:sqls){
	    	 log.info(sql);
	    	 stmt.addBatch(sql);
	     }
	     return stmt.executeBatch();
    }
    
    public static void executeBatchs(List<String[]> sqls) throws Exception
    {
	     if(sqls==null)
	    	 return ;
	     
	     List<String> executesql=new ArrayList<String>();
	     int BatchNum=100;
    	 String strSqls="";
    	 int i=1;
	      for(String[] sql:sqls){
	    	  if(i==1)
	    		  strSqls+=sql[2]+",";
    		  else
    			  strSqls+=sql[1]+",";
	    	  
	    	  if(i==BatchNum){
	    		  strSqls=strSqls.substring(0, strSqls.length()-1);
	    		  executesql.add(strSqls);
	    		  strSqls="";
	    		  i=0;
	    	  }
	    	  i++;
	      }
	      strSqls=strSqls.substring(0, strSqls.length()-1);
	      executesql.add(strSqls);i--;
	      stmt=conn.createStatement();
	      
	      for(String sql:executesql){
	    	  log.info(sql);
	    	  stmt.execute(sql);
	      }
    }
    
    //开启事务
    public void startTrans() throws Exception {
		conn.setAutoCommit(false);
    }
    
    //提交事务
    public void commit() throws Exception {
		conn.commit();
		conn.setAutoCommit(true);
    }
    
    //回滚事务
    public void rollback() throws Exception {
		conn.rollback();
		conn.setAutoCommit(true);	
    }
   
    public void close() throws Exception {
    	try{
    		if(null != conn) conn.close();
    		if(null != prst) prst.close();
    		if(null != rst) rst.close();
    		if(null != stmt) stmt.close();
    	} catch (SQLException e) {
    		throw new Exception("关闭连接时失败：" + e.getMessage());
    	}
    }
    
    public void free() throws Exception {
    	try{
    		prst.clearParameters();
    	} catch (SQLException e) {
    		throw new Exception("清除当前的查询值时出错：" + e.getMessage());
    	}
    }
}
